package org.liaohailong.library.db

import android.content.Context
import android.database.Cursor
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import android.util.Log
import org.liaohailong.library.db.builder.SQLHelper
import org.liaohailong.library.db.builder.SQLHelperFactory
import org.liaohailong.library.db.util.Utility

/**
 * Author: liaohailong
 * E-mail: liaohailong190@foxmail.com
 * Date: 2019/4/3
 * Time: 3:32 PM
 * Description: 数据库操作类
 *
 * 1，基本的增删改查，链式调用
 *
 * 2，查询功能增强：
 *    2.1，distinct去重
 *    2.2，order by排序
 *    2.3，limit + offset 分页查询
 *
 * 3，表更新，自动同步旧数据，自动判断列的新增/移除
 *
 * 4，插入功能增强：
 *    4.1，数据排序
 *    4.2，sql语句合并
 *    4.3，事务提交
 *
 */
object SQLite {
    private const val TAG = "SQLite"
    private lateinit var sqLiteOpenHelper: SQLiteOpenHelper

    private var dbName: String = "SQLite.db"
    private var dbVersion: Int = 1
    private val tables: MutableList<Class<*>> = mutableListOf()

    private var logEnable = true

    /**
     * 初始化
     * 建议在Application初始化时调用
     * @param context application
     * @param dbName 数据库名称
     * @param dbVersion 数据库版本号
     * @param logEnable 是否打印log日志
     * @param table 表映射
     */
    fun init(context: Context, dbName: String, dbVersion: Int, logEnable: Boolean, vararg table: Class<*>) {
        this.dbName = dbName
        this.dbVersion = dbVersion
        tables.addAll(table)
        sqLiteOpenHelper = VictorSQLiteOpenHelper(context, dbName, dbVersion)
        setLogEnable(logEnable)
    }

    fun setLogEnable(logEnable: Boolean) {
        this.logEnable = logEnable
    }

    fun isLogEnable(): Boolean {
        return logEnable
    }

    fun getDBName(): String = dbName
    fun getDBVersion(): Int = dbVersion
    fun getTables(): List<Class<*>> = tables
    fun <T> table(table: Class<T>): Options<T> = Options(table)

    private fun getWriter(): SQLiteDatabase = sqLiteOpenHelper.writableDatabase
    private fun getReader(): SQLiteDatabase = sqLiteOpenHelper.readableDatabase

    private fun log(msg: String) {
        if (isLogEnable()) {
            Log.i(TAG, msg)
        }
    }

    class Options<T>(private var table: Class<T>) {
        private val sqlHelper: SQLHelper = SQLHelperFactory.get().apply { setTable(table) }
        private var distinct: Boolean = false
        private var select: MutableList<String> = mutableListOf()
        private var set: MutableMap<String, Any> = mutableMapOf()
        private var where: MutableMap<String, Any> = mutableMapOf()
        private var order: MutableList<String> = mutableListOf()
        private var orderBy: OrderBy = OrderBy.ASC
        private var limit: Int = 0
        private var offset: Int = 0

        fun distinct(distinct: Boolean): Options<T> {
            this.distinct = distinct
            return this
        }

        fun select(select: MutableList<String>): Options<T> {
            this.select.addAll(select)
            return this
        }

        fun set(set: Map<String, Any>): Options<T> {
            this.set.putAll(set)
            return this
        }

        fun where(where: MutableMap<String, Any>): Options<T> {
            this.where.putAll(where)
            return this
        }

        fun where(item: T): Options<T> {
            val whereParams = Utility.getWhereParams(item)
            where(whereParams)
            return this
        }

        fun order(order: MutableList<String>, orderBy: OrderBy): Options<T> {
            this.order.addAll(order)
            this.orderBy = orderBy
            return this
        }

        fun limit(limit: Int): Options<T> {
            this.limit = Math.max(0, limit)
            return this
        }

        fun offset(offset: Int): Options<T> {
            this.offset = Math.max(0, offset)
            return this
        }

        private fun getSQLHelper(): SQLHelper {
            sqlHelper.apply {
                setSelectColumns(select)
                set.entries.forEach { putUpdateParam(it.key, it.value) }
                where.entries.forEach { putWhereParam(it.key, it.value) }
                order.forEach { putOrder(it) }
                putLimit(limit, offset)
            }
            return sqlHelper
        }

        /**
         * 插入单条数据
         * 插入海量数据请使用->multipleInsert()
         * @param item 数据
         */
        fun insert(item: Any): Int {
            var result = 0
            log("insert准备获取sqlHelper对象")
            val sqlHelper = getSQLHelper()
            log("insert#sqlHelper对象获取完毕，准备开始解析item数据")
            sqlHelper.saveItem(item)
            log("insert#item数据解析完毕，开始准备插入数据库")
            val tableName = sqlHelper.getTable()
            val contentValues = sqlHelper.getContentValues()
            try {
                getWriter().insert(tableName, null, contentValues)
                result++
                log("insert数据库插入完毕")
            } catch (e: Exception) {
                result = 0
                log("insert数据库插入异常，e=$e")
            }
            return result
        }

        /**
         * 插入海量数据
         * @param insert 大量数据 实测:小米5s 100000条简单数据 插入耗时2500ms左右
         */
        fun multipleInsert(insert: MutableList<T>): Int {
            var result = insert.size
            val sqlHelper = getSQLHelper()
            log("multipleInsert准备构建合并sql")
            val sql = sqlHelper.getMultipleInsertSQL(insert)
            log("multipleInsert合并sql构建完毕")
            val writer = getWriter()
            log("开始使用事务方式插入")
            writer.beginTransaction()
            try {
                writer.execSQL(sql)
                writer.setTransactionSuccessful()
                log("使用事务方式插入完毕")
            } catch (e: Exception) {
                log("使用事务方式插入失败了，e=$e")
                result = 0
            } finally {
                writer.endTransaction()
                writer.close()
            }
            return result
        }

        fun delete(): Int {
            val sqlHelper = getSQLHelper()
            val tableName = sqlHelper.getTable()
            val whereClause = sqlHelper.getWhereClause()
            val whereArgs = sqlHelper.getWhereArgs()
            return getWriter().delete(tableName, whereClause, whereArgs)
        }

        fun update(): Int {
            val sqlHelper = getSQLHelper()
            val tableName = sqlHelper.getTable()
            val contentValues = sqlHelper.getContentValues()
            val whereClause = sqlHelper.getWhereClause()
            val whereArgs = sqlHelper.getWhereArgs()
            return getWriter().update(tableName, contentValues, whereClause, whereArgs)
        }

        fun select(): List<T> {
            val sqlHelper = getSQLHelper()
            val tableName = sqlHelper.getTable()
            val selectColumns = sqlHelper.getSelectColumns()?.toTypedArray()
            val whereClause = sqlHelper.getWhereClause()
            val whereArgs = sqlHelper.getWhereArgs()
            val orderBy = sqlHelper.getOrderBy(orderBy.getSQLWord())
            val limit = sqlHelper.getLimit()
            val cursor = getReader().query(
                    distinct,
                    tableName,
                    selectColumns,
                    whereClause,
                    whereArgs,
                    null,
                    null,
                    orderBy,
                    limit)
            val result = mutableListOf<T>()
            if (cursor.moveToFirst()) {
                val parser = SQLItemParser(table)
                do {
                    result.add(parser.parse(cursor))
                } while (cursor.moveToNext())
            }
            cursor.close()
            return result
        }

        fun execSQL(sql: String, bindArgs: Array<Any>) {
            getWriter().execSQL(sql, bindArgs)
        }

        fun rawQuery(sql: String, selectionArgs: Array<String>): Cursor {
            return getReader().rawQuery(sql, selectionArgs)
        }

        enum class OrderBy(private val sql: String) {
            ASC("ASC"),//从小到大
            DESC("DESC");//从大到小

            fun getSQLWord(): String = sql
        }
    }
}
